﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_SMS_GetMobileNoByUsername]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_SMS_GetMobileNoByUsername];
GO
CREATE PROCEDURE [dbo].[sproc_SMS_GetMobileNoByUsername]
    @UserNameStr nvarchar(3000),
    @MobileNoStr nvarchar(3000) output
/*

===================================================
功能:    得到在线人数
参数:
    @Username         nvarchar;         用户名字符串
===================================================

*/
AS
BEGIN
SET NOCOUNT ON

declare @Str nvarchar(3000)
declare @UserName nvarchar(300)
declare @QuoteUserNameStr nvarchar(3000)
declare @tmp table (
	staff_name nvarchar(300) collate Chinese_PRC_CI_AS null
);

set @MobileNoStr = '';
set @UserName = '';
set @Str = @UserNameStr
set @QuoteUserNameStr = '';

while len(@Str)>0
begin
    --如果不是最后一次
    if(charindex(',',@Str)>0)
    begin
		--截取逗号前数据
		set @UserName = substring(@Str,1,charindex(',',@Str)-1)

		--剪切字符串
		select @Str = substring(@Str,charindex(',',@Str)+1,LEN(@Str))

		insert into @tmp values(@UserName)
		--set @quoteusernamestr = @QuoteUserNameStr + QUOTENAME(@UserName,'''') + ','
    end
    else
    begin
        --set @quoteusernameStr = @QuoteUserNameStr + QUOTENAME(@Str,'''')
        insert into @tmp values(@Str)
        break
    end
end

select
	@MobileNoStr = @MobileNoStr + (case when A.Mobile is null then '' else A.Mobile end) + ','
from
	@tmp B
    left outer join uds_staff A on A.staff_name = B.staff_name

if right(@MobileNoStr,1)= ','
    set @MobileNoStr = left(@MobileNoStr,LEN(@MobileNoStr)-1)
select @MobileNoStr as 'Mobile'

--print @MobileNoStr
END
GO